﻿using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace DataAccess.Tests
{
    [TestClass()]
    public class SqlServer2008DatabaseTransactionTest
    {
        private TestContext testContextInstance;

        public TestContext TestContext
        {
            get
            {
                return testContextInstance;
            }
            set
            {
                testContextInstance = value;
            }
        }

        #region Additional test attributes
        // 
        //You can use the following additional attributes as you write your tests:
        //
        //Use ClassInitialize to run code before running the first test in the class
        //[ClassInitialize()]
        //public static void MyClassInitialize(TestContext testContext)
        //{
        //}
        //
        //Use ClassCleanup to run code after all tests in a class have run
        //[ClassCleanup()]
        //public static void MyClassCleanup()
        //{
        //}
        //
        //Use TestInitialize to run code before running each test
        //[TestInitialize()]
        //public void MyTestInitialize()
        //{
        //}
        //
        //Use TestCleanup to run code after each test has run
        //[TestCleanup()]
        //public void MyTestCleanup()
        //{
        //}
        //
        #endregion

        readonly string connectionName = "SqlServerDataAccessTest.LocalTransactionTest.ConnectionString";

        internal class Account
        {
            public int AccountId { get; set; }
            public decimal Amount { get; set; }
        }

        [TestMethod()]
        public void DataAccessLocalTransactionTest()
        {
            // Test script executor (create database)
            ScriptExecutor.ExecuteScript(ConnectionManager.GetConnection("master"),
@"
USE master
GO

IF EXISTS
(
    SELECT NAME
    FROM Sys.Databases
    WHERE Name = N'LocalTransactionTest'
)
BEGIN
    DROP DATABASE LocalTransactionTest
END
GO

CREATE DATABASE LocalTransactionTest
GO

USE LocalTransactionTest
GO

CREATE TABLE LocalTransactionTest..CheckingAccount(
    AccountId INT NOT NULL,
    Amount MONEY
)

ALTER TABLE LocalTransactionTest..CheckingAccount
ADD CONSTRAINT CheckingAccount_PK PRIMARY KEY (AccountId)
GO

CREATE TABLE LocalTransactionTest..SavingAccount(
    AccountId INT NOT NULL,
    Amount MONEY
)

ALTER TABLE LocalTransactionTest..SavingAccount
ADD CONSTRAINT SavingAccount_PK PRIMARY KEY (AccountId)
GO

INSERT INTO LocalTransactionTest..CheckingAccount (AccountId, Amount) VALUES (1, 1000)
GO

INSERT INTO LocalTransactionTest..SavingAccount (AccountId, Amount) VALUES (1, 500)
GO
",
            "^GO");

            // Transaction with successfull commit

            Factory
                .Transaction()
                .IsLocal()
                .ReadCommitted()
                .Connection(connectionName)
                .Command( // Withdraw 300 from the checking account
                    Factory
                        .Command()
                        .Text("UPDATE LocalTransactionTest..CheckingAccount SET Amount = Amount - 300 WHERE AccountId = @accountId")
                        .Parameter("accountId", 1)
                )
                .Command( // Deposit 300 into the saving account
                    Factory
                        .Command()
                        .Text("UPDATE LocalTransactionTest..SavingAccount SET Amount = Amount + 300 WHERE AccountId = @accountId")
                        .Parameter("accountId", 1)
                )
                .Execute();

            var checkingCommand = Factory
                .Command()
                .Connection(connectionName)
                .Text("SELECT Amount FROM LocalTransactionTest..CheckingAccount WHERE AccountId = @accountId")
                .Parameter("accountId", 1)
                .ToQueryObject<Account>((reader, account) =>
                {                  
                    account.AccountId = 1;
                    account.Amount = reader.GetDecimal(0);
                });

            checkingCommand.Execute();

            Account checking = checkingCommand.Object;

            Assert.AreEqual(700m, checking.Amount);

            var savingCommand = Factory
                .Command()
                .Connection(connectionName)
                .Text("SELECT Amount FROM LocalTransactionTest..SavingAccount WHERE AccountId = @accountId")
                .Parameter("accountId", 1)
                .ToQueryObject<Account>((reader, account) =>
                {
                    account.AccountId = 1;
                    account.Amount = reader.GetDecimal(0);
                });

            savingCommand.Execute();

            Account saving = savingCommand.Object;

            Assert.AreEqual(800m, saving.Amount);

            // Transaction with rollback

            bool failed = false;

            try
            {
                Factory
                    .Transaction()
                    .IsLocal()
                    .ReadCommitted()
                    .Connection(connectionName)
                    .Command( // Withdraw 300 from the checking account
                        Factory
                            .Command()
                            .Text("UPDATE LocalTransactionTest..CheckingAccount SET Amount = Amount - 300 WHERE AccountId = @accountId")
                            .Parameter("accountId", 1)
                    )
                    .Command( // Deposit 300 into the saving account but comment the parameter so it will fail
                        Factory
                            .Command()
                            .Text("UPDATE LocalTransactionTest..SavingAccount SET Amount = Amount + 300 WHERE AccountId = @accountId")
                    //.Parameter("accountId", 1)
                    )
                    .Execute();
            }
            catch
            {
                failed = true;
            }

            Assert.IsTrue(failed);

            var response = checkingCommand.QueryObject<Account>((reader, account) =>
            {
                account.AccountId = 1;
                account.Amount = reader.GetDecimal(0);
            });

            checking = response.Data;

            Assert.AreEqual(700m, checking.Amount);

            response = savingCommand.QueryObject<Account>((reader, account) =>
            {
                account.AccountId = 1;
                account.Amount = reader.GetDecimal(0);
            });

            saving = response.Data;

            Assert.AreEqual(800m, saving.Amount);
        }
    }
}
